﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using CRMFramework;

namespace CRMBiz.Contact
{
    public static class PropertyHandler
    {
        /// <summary>
        /// function to get Property info list
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns>PropertyData</returns>
        public static DataSet SelectProperty(int pageIndex, int pageSize, int CurrentContact_id)
        {
            DataSet ds;
            try
            {
                var sbSql = new StringBuilder("SELECT *,DATE_FORMAT(CompletionDate,'%d-%b-%Y') as CompDate,(select count(*) from crm_property where CurrentContact_id=" + CurrentContact_id + ") as Total from crm_property where CurrentContact_id=" + CurrentContact_id + " order by Address desc ");
                sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /// <summary>
        /// function to update Property info
        /// </summary>
        ///<param name="PropertyID"></param>
        /// <returns>PropertyData</returns>
        public static int EditPropertyInfo(int PropertyID, int CurrentContact_id, string Address, string Arrears, string Owner, string PropertyType, string Description, string LandSize, string Bedroom, string Bathroom, string MortgageOwing, string MortgagePayment, string MortgageLender, string TitleRef, string SecurityStatus, string InterestType, string LodgmentDate, string CompletionDate, string PropertyValue, string ValueSource, string Mortgagee, string SubSequentParties, string PropertyComment)
        {
            int intReturnValue = 0;
            try
            {
                DateTime p_LodgmentDate = new DateTime();
                DateTime p_CompletionDate = new DateTime();
                DateTimeFormatInfo p_date = new DateTimeFormatInfo();
                p_date.ShortDatePattern = "yyyy-MM-dd";
                p_date.DateSeparator = "-";

                if (!string.IsNullOrEmpty(LodgmentDate))
                    p_LodgmentDate = Convert.ToDateTime(LodgmentDate, p_date);

                if (!string.IsNullOrEmpty(CompletionDate))
                    p_CompletionDate = Convert.ToDateTime(CompletionDate, p_date);

                var para = new[] {
                     DBhelper.SetParameter("@PropertyID", PropertyID),
                    DBhelper.SetParameter("@CurrentContact_id", CurrentContact_id),
                    DBhelper.SetParameter("@Address", Address),
                    DBhelper.SetParameter("@Arrears", Arrears),
                    DBhelper.SetParameter("@Owner", Owner),
                    DBhelper.SetParameter("@PropertyType", PropertyType),
                    DBhelper.SetParameter("@Description", Description),
                    DBhelper.SetParameter("@LandSize", LandSize),
                    DBhelper.SetParameter("@Bedroom",Bedroom),
                    DBhelper.SetParameter("@Bathroom",Bathroom),
                    DBhelper.SetParameter("@MortgageOwing",MortgageOwing),
                    DBhelper.SetParameter("@MortgagePayment",MortgagePayment),
                    DBhelper.SetParameter("@MortgageLender",MortgageLender),
                    DBhelper.SetParameter("@TitleRef",TitleRef),
                    DBhelper.SetParameter("@SecurityStatus",SecurityStatus),
                    DBhelper.SetParameter("@InterestType",InterestType),   
                    DBhelper.SetParameter("@LodgmentDate",p_LodgmentDate),
                    DBhelper.SetParameter("@CompletionDate",p_CompletionDate),   
                    DBhelper.SetParameter("@PropertyValue",PropertyValue),
                    DBhelper.SetParameter("@ValueSource",ValueSource),   
                    DBhelper.SetParameter("@Mortgagee",Mortgagee),
                    DBhelper.SetParameter("@SubSequentParties",SubSequentParties),   
                    DBhelper.SetParameter("@PropertyComment",PropertyComment),   
                };

                string sbSql = "update crm_property set Address =@Address,Arrears=@Arrears,Owner=@Owner,PropertyType=@PropertyType,Description=@Description,LandSize=@LandSize,Bedroom=@Bedroom,Bedroom=@Bedroom,MortgageOwing=@MortgageOwing,MortgagePayment=@MortgagePayment,MortgageLender=@MortgageLender,TitleRef=@TitleRef,SecurityStatus=@SecurityStatus,InterestType=@InterestType,LodgmentDate=@LodgmentDate,CompletionDate=@CompletionDate,PropertyValue=@PropertyValue,ValueSource=@ValueSource,Mortgagee=@Mortgagee,SubSequentParties=@SubSequentParties,PropertyComment=@PropertyComment where PropertyID = @PropertyID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /// <summary>
        /// function to add new Property info
        /// </summary>
        /// <param name="Contact_ID"></param>
        /// <returns>dataset</returns>
        public static int AddPropertyInfo(string Address, string Arrears, string Owner, string PropertyType, string Description, string LandSize, string Bedroom, string Bathroom, string MortgageOwing, string MortgagePayment, string MortgageLender, string TitleRef, string SecurityStatus, string InterestType, string LodgmentDate, string CompletionDate, string PropertyValue, string ValueSource, string Mortgagee, string SubSequentParties, string PropertyComment,int CurrentContact_id )
        {
            int intReturnValue = 0;
            try
            {
                DateTime p_LodgmentDate = new DateTime();
                DateTime p_CompletionDate = new DateTime();
                DateTimeFormatInfo p_date = new DateTimeFormatInfo();
                p_date.ShortDatePattern = "yyyy-MM-dd";
                p_date.DateSeparator = "-";

                if (!string.IsNullOrEmpty(LodgmentDate))
                    p_LodgmentDate = Convert.ToDateTime(LodgmentDate, p_date);

                if (!string.IsNullOrEmpty(CompletionDate))
                    p_CompletionDate = Convert.ToDateTime(CompletionDate, p_date);

                var para = new[] {
                    DBhelper.SetParameter("@Address", Address),
                    DBhelper.SetParameter("@Arrears", Arrears),
                    DBhelper.SetParameter("@Owner", Owner),
                    DBhelper.SetParameter("@PropertyType", PropertyType),
                    DBhelper.SetParameter("@Description", Description),
                    DBhelper.SetParameter("@LandSize", LandSize),
                    DBhelper.SetParameter("@Bedroom",Bedroom),
                    DBhelper.SetParameter("@Bathroom",Bathroom),
                    DBhelper.SetParameter("@MortgageOwing",MortgageOwing),
                    DBhelper.SetParameter("@MortgagePayment",MortgagePayment),
                    DBhelper.SetParameter("@MortgageLender",MortgageLender),
                    DBhelper.SetParameter("@TitleRef",TitleRef),
                    DBhelper.SetParameter("@SecurityStatus",SecurityStatus),
                    DBhelper.SetParameter("@InterestType",InterestType),   
                    DBhelper.SetParameter("@LodgmentDate",p_LodgmentDate),
                    DBhelper.SetParameter("@CompletionDate",p_CompletionDate),   
                    DBhelper.SetParameter("@PropertyValue",PropertyValue),
                    DBhelper.SetParameter("@ValueSource",ValueSource),   
                    DBhelper.SetParameter("@Mortgagee",Mortgagee),
                    DBhelper.SetParameter("@SubSequentParties",SubSequentParties),   
                    DBhelper.SetParameter("@PropertyComment",PropertyComment),   
                    DBhelper.SetParameter("@CurrentContact_id", CurrentContact_id),
                };

                string sbSql = "insert into crm_property(Address,Arrears,Owner,PropertyType,Description,LandSize,Bedroom,Bathroom,MortgageOwing,MortgagePayment,MortgageLender,TitleRef,SecurityStatus,InterestType,LodgmentDate,CompletionDate,PropertyValue,ValueSource,Mortgagee,SubSequentParties,PropertyComment,CurrentContact_id) values(@Address,@Arrears,@Owner,@PropertyType,@Description,@LandSize,@Bedroom,@Bathroom,@MortgageOwing,@MortgagePayment,@MortgageLender,@TitleRef,@SecurityStatus,@InterestType,@LodgmentDate,@CompletionDate,@PropertyValue,@ValueSource,@Mortgagee,@SubSequentParties,@PropertyComment,@CurrentContact_id)";
                DBhelper.ExecuteNonQuery(sbSql, para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        /// <summary>
        /// function to delete property info
        /// </summary>
        /// <param name="PropertyID"></param>
        /// <returns>dataset</returns>
        public static bool DeletePropertyInfo(int PropertyID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@PropertyID", PropertyID),
                };

                DBhelper.ExecuteNonQuery("delete from crm_property where PropertyID=@PropertyID", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
